﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Windows.Forms;
using DevExpress.XtraEditors; 
using UFIDA.U8C.AnLianDB;
using UFIDA.U8.DB;
using UFIDA.U8.PUB;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
using System.Data.SqlClient;
using UFIDA.U8C.AnLianDB;
using Newtonsoft.Json.Linq;

namespace AnLianSysClient
{
   
    public partial class frmCA : DevExpress.XtraEditors.XtraForm
    {
        private string _year = DateTime.Now.ToString("yyyy");
        const string regKey = "DevExpress\\XtraGrid\\Layouts\\CA";
        private string _corp = "";
        private static string u8curl = common.getConfigInfo("u8curl");
        private static string glurl = u8curl + "/u8cloud/api/gl/voucher/insert";

        public frmCA()
        {
            InitializeComponent();
        } 

        private void FrmCA_Load(object sender, EventArgs e)
        {
            gdCA.MainView.RestoreLayoutFromRegistry(regKey);//读取（还原）布局 
            this.AddCorp();
        }
        private void AddCorp()
        {
            dscorp = UFIDA.U8C.AnLianDB.common.GetU8CCorp();
            if (dscorp != null && dscorp.Tables.Count > 0 && dscorp.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in dscorp.Tables[0].Rows)
                {
                    cbcorp.Properties.Items.Add(dr["unitname"].ToString());
                }
            }
        } 
        private void FrmCA_FormClosed(object sender, FormClosedEventArgs e)
        {
            gdCA.MainView.SaveLayoutToRegistry(regKey); //保存布局
        }

        private void BtnGL_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (cbcorp.Text.IsNullOrEmpty())
            {
                MessageBox.Show("请先选择公司！");
                return;
            }
            string dbgndate = edtBgndate.Text;
            string denddate = edtEnddate.Text;
            if (string.IsNullOrEmpty(dbgndate) || string.IsNullOrEmpty(denddate))
            {
                MessageBox.Show("请先选择开始结束日期");
                return;
            }

            if (MessageBox.Show("生成本月资产折旧项目分摊凭证，请确认继续", "警告", MessageBoxButtons.OKCancel) == DialogResult.OK)
            {
                int year = edtBgndate.DateTime.Year;
                int month = edtBgndate.DateTime.Month;
                string corp = _corp;
                //生成凭证
                this.sendGL(year, month, corp, UFIDA.U8C.AnLianDB.common.bankconn());
            }
        }

        private void sendGL(int year, int month, string corp, string conn)
        {
            string errMsg = "";
            string resResult = "";
            try
            {
                List<custwork_zj_avg> mlist = DAL_custwork_zj_avg.GetModel(year, month, corp, conn);
                if (mlist != null && mlist.Count > 0)
                {
                    string js = this.getGLjs(mlist);
                    if (js == "")
                    {
                        MessageBox.Show("凭证JS空,请检查");
                    }
                    bool bpost = common.PostU8C(Guid.NewGuid().ToString(), js, glurl, ref resResult, ref errMsg);
                    if (!string.IsNullOrEmpty(errMsg))
                    {
                        MessageBox.Show("生成凭证失败：" + errMsg);
                    }
                    else
                    {
                        MessageBox.Show("生成凭证完成");
                    }
                    
                }
            }
            catch (Exception e)
            {
                errMsg = "生成凭证失败" + e.Message;
                UFIDA.U8.PUB.LogHelper.WriteLine(errMsg);
                throw new Exception(errMsg);
            }
        }

        private string getGLjs(List<custwork_zj_avg> mlist)
        {
            JArray items = new JArray();
            //decimal jf = 0;
            //decimal df = 0;
            foreach (custwork_zj_avg item in mlist)
            {
                JObject detail = new JObject();
                //收款付款
                detail["explanation"] = "经营性固定资产折旧及费用分摊";
                JArray ass = new JArray();
                detail["pk_accsubj"] = item.ccode2.IsNullOrEmpty() ? item.ccode : item.ccode2;

                if (!item.project.IsNullOrEmpty())
                {
                    ass.Add(new JObject()
                    {
                        { "checktypecode","J1Ass"},
                        { "checkvaluecode",item.project}
                    });
                }
                if (!item.cdeptcode.IsNullOrEmpty())
                {
                    ass.Add(new JObject()
                    {
                        { "checktypecode","2"},
                        { "checkvaluecode",item.cdeptcode}
                    });
                }

                detail["ass"] = ass;

                detail["pk_currtype"] = "CNY";
                detail["debitamount"] = item.jf.ToString();
                detail["localdebitamount"] = item.jf.ToString();
                detail["creditamount"] = item.df.ToString();
                detail["localcreditamount"] = item.df.ToString(); 
                items.Add(detail);
                //df += item.df;
                //jf += item.jf;
            }

            //借方
            //foreach (custwork_zj_avg item in mlist)
            //{
            //    JObject detail = new JObject();
            //    //收款付款
            //    detail["explanation"] = "经营性固定资产折旧及费用分摊";
            //    JArray ass = new JArray(); 
            //    detail["pk_accsubj"] = item.ccode2;
               
            //    ass.Add(new JObject()
            //        {
            //            { "checktypecode","J1Ass"},
            //            { "checkvaluecode",item.project}
            //        });

            //    detail["ass"] = ass;
            //    detail["pk_currtype"] = "CNY";
            //    detail["debitamount"] = item.avgAmount;
            //    detail["localdebitamount"] = item.avgAmount;
            //    items.Add(detail);
            //}
            ////贷方

            //foreach (custwork_zj_avg item in mlist)
            //{
            //    JObject detail = new JObject();
            //    //收款付款
            //    detail["explanation"] = "经营性固定资产折旧及费用分摊";
            //    //辅助核算
            //    detail["pk_accsubj"] = item.ccode;
            //    if (!string.IsNullOrEmpty(item.cdeptcode))
            //    {
            //        JArray ass = new JArray();
            //        ass.Add(new JObject()
            //        {
            //            { "checktypecode","2"},
            //            { "checkvaluecode",item.cdeptcode}
            //        });

            //        detail["ass"] = ass;
            //    }
            //    detail["pk_currtype"] = "CNY";
            //    if (item.ccode.Equals("640103"))
            //    {
            //        detail["debitamount"] = 0-item.avgAmount;
            //        detail["localdebitamount"] = 0-item.avgAmount; 
            //    }
            //    else
            //    {
            //        detail["creditamount"] = item.avgAmount;
            //        detail["localcreditamount"] = item.avgAmount; 
            //    }
               
            //    items.Add(detail);
            //}

            JObject req = new JObject();
            JArray head = new JArray();
            JObject voucher = new JObject();
            voucher["pk_corp"] = _corp;
            voucher["pk_glorgbook"] = UFIDA.U8C.AnLianDB.common.GetU8CGlorgBook(_corp);
            voucher["pk_prepared"] = common.getConfigInfo("u8cgluser"); // "18606510717";
            voucher["pk_vouchertype"] = "记账";
            voucher["prepareddate"] = edtEnddate.Text;
            voucher["details"] = items;
            head.Add(voucher);
            req["voucher"] = head;
            return req.ToString();
        }
         

        private void BtnFT_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (cbcorp.Text.IsNullOrEmpty())
            {
                MessageBox.Show("请先选择公司！");
                return;
            }
            string dbgndate = edtBgndate.Text;
            string denddate = edtEnddate.Text;
            if (string.IsNullOrEmpty(dbgndate) || string.IsNullOrEmpty(denddate))
            {
                MessageBox.Show("请先选择开始结束日期");
                return;
            }
            if (MessageBox.Show("计算成本结转将清除本月及后续月份已计算数据，请确认继续", "警告", MessageBoxButtons.OKCancel) == DialogResult.OK)
            {
                int year = edtBgndate.DateTime.Year;
                int month = edtBgndate.DateTime.Month;
                try
                {
                    //读取U8C科目余额
                    bool bsync = this.getZjBalance(year, month, _corp, UFIDA.U8C.AnLianDB.common.bankconn());
                    if (!bsync )
                    {
                        return;
                    }
                    //计算结转数据
                    bool bcalc = this.getcalcZj(year, month, _corp, UFIDA.U8C.AnLianDB.common.bankconn());

                    MessageBox.Show("费用分摊完成");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }  
        }

        private bool getcalcZj(int year, int month, string corp, string connstr)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        conn.Open();
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.CommandText = "sp_cust_zj_calc_U8C";
                        cmd.CommandTimeout = 10000;
                        cmd.Connection = conn;
                        SqlParameter para1 = new SqlParameter("@year", System.Data.SqlDbType.Int, 4);
                        para1.Value = year;
                        cmd.Parameters.Add(para1);
                        SqlParameter para2 = new SqlParameter("@month", System.Data.SqlDbType.Int, 4);
                        para2.Value = month;
                        cmd.Parameters.Add(para2);
                        SqlParameter para3 = new SqlParameter("@corp", System.Data.SqlDbType.VarChar, 50);
                        para3.Value = Convert.ToInt32(corp);
                        cmd.Parameters.Add(para3);
                        cmd.ExecuteNonQuery();
                    }
                }
                return true;
            }
            catch (Exception e)
            {
                string errMsg = "sp_cust_work_calc 执行存储过程失败" + e.Message;
                UFIDA.U8.PUB.LogHelper.WriteLine(errMsg);
                throw new Exception(errMsg);
            }
        }

        /// <summary>
        /// 取U8C总账费用
        /// </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="corp"></param>
        /// <returns></returns>
        private bool getZjBalance(int year, int month, string corp, string conn)
        {
            string sql = "delete custwork_zj_period where iyear = " + year + " and imonth = " + month + " and corp = " + corp;
            UFIDA.U8.DB.DbHelperSQL.ExecuteSql(sql, conn);
            sql = "select * from vw_cust_zjmonth where debitamount > 0 and unitcode = '" + corp + "' and iyear = " + year + "and imonth = " + month;
            DataTable dt = UFIDA.NC.DB.DbHelperSQL_Ora.QueryDTOra(sql, "custwork_zj_period", UFIDA.U8C.AnLianDB.common.u8cconn());
            if (dt == null || dt.Rows.Count == 0)
            {
                MessageBox.Show("没有获取到当月折旧数据");
                return false;
            }
            UFIDA.U8.DB.DbHelperSQL.ImportDataByDataTable2(dt, "custwork_zj_period", UFIDA.U8C.AnLianDB.common.bankconn());

            return true;
        }

        private void BtnQuery_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            this.Query();
        }

        private void Query()
        {
            if (cbcorp.Text.IsNullOrEmpty())
            {
                MessageBox.Show("请先选择公司！");
                return;
            }
            StringBuilder sql = new StringBuilder();
            sql.Append("select *, case iflag when 1 then '是' else '否' end cflag from  wazh_cazj where 1=1 ");

            if (!string.IsNullOrEmpty(edtCode.Text))
            {
                sql.Append(" and project like '%" + edtCode.Text + "%'");
            }

            if (!string.IsNullOrEmpty(edtName.Text))
            {
                sql.Append(" and projectname like '%" + edtName.Text + "%'");
            }

            if (!string.IsNullOrEmpty(cbcorp.Text))
            {
                sql.Append(" and corp = '" + _corp + "'");
            }

            if (!string.IsNullOrEmpty(cbsync.Text))
            {
                if (cbsync.Text.Equals("是"))
                {
                    sql.Append(" and iflag = 1");
                }
                else
                {
                    sql.Append(" and iflag = 0");
                }
            }

            if (!string.IsNullOrEmpty(edtclass.Text))
            {
                sql.Append(" and projectclass = '" + edtclass.Text + "'");
            }

            if (!string.IsNullOrEmpty(edtBgndate.Text))
            {
                sql.Append(" and ddate >='" + edtBgndate.Text + "'");
            }
            if (!string.IsNullOrEmpty(edtEnddate.Text))
            {
                sql.Append(" and ddate <='" + edtEnddate.Text + "'");
            }



            StringBuilder sqlavg = new StringBuilder();
            sqlavg.Append("select iyear as 年, imonth as 月, project 项目编码, projectname 项目名称, projectclass 项目类型 , ccode 科目编码, ccodename 科目名称, avgAmount 分摊金额, corp 公司编码 from  custwork_zj_avg  where  1=1 ");

            if (!string.IsNullOrEmpty(edtCode.Text))
            {
                sqlavg.Append(" and citemcode like '%" + edtCode.Text + "%'");
            }

            if (!string.IsNullOrEmpty(edtName.Text))
            {
                sqlavg.Append(" and projectname like '%" + edtName.Text + "%'");
            }

            if (!string.IsNullOrEmpty(cbcorp.Text))
            {
                sqlavg.Append(" and corp = '" + _corp + "'");
            }

            if (!string.IsNullOrEmpty(edtclass.Text))
            {
                sqlavg.Append(" and projectclass = '" + edtclass.Text + "'");
            }

            if (!string.IsNullOrEmpty(edtBgndate.Text))
            {
                sqlavg.Append(" and iyear = year('" + edtBgndate.Text + "') and imonth =month('" + edtBgndate.Text + "')");
            }


            gdCA.DataSource = null;
            dsCA = DbHelperSQL.Query(sql.ToString(), "wazh_cazj", UFIDA.U8C.AnLianDB.common.bankconn());
            gdCA.DataSource = bdsCA;
            bdsCA.DataSource = dsCA;
            bdsCA.DataMember = "wazh_cazj";

            gdcreport.DataSource = null;
            DataTable dtreport = DbHelperSQL.QueryAsDT(sqlavg.ToString(), UFIDA.U8C.AnLianDB.common.bankconn());
            gdcreport.DataSource = dtreport;
            gdcreport.DataMember = dtreport.TableName; 
        }

        private void btnImportHX_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (cbcorp.Text.IsNullOrEmpty())
            {
                MessageBox.Show("请先选择公司！");
                return;
            }
            string dbgndate = edtBgndate.Text;
            string denddate = edtEnddate.Text;
            if (string.IsNullOrEmpty(dbgndate) || string.IsNullOrEmpty(denddate))
            {
                MessageBox.Show("请先选择开始结束日期");
                return;
            }
            string sqld = "delete from wazh_cazj where ddate >= '" + dbgndate + "' and ddate <='" + denddate + "'"; 
            StringBuilder sql = new StringBuilder();
            sql.Append("select project, projectname, ddate , projectclass, 0 as id, 0 as iflag, day as dayqty, corp  from  wazh_cazj where iflag = 0 and ddate >='"+ dbgndate +"' and ddate <='"+ denddate +"'");
            try
            {
                dsCA = UFIDA.MYSQL.DB.DbHelperSQL.Query(sql.ToString(), "wazh_cazj", UFIDA.U8C.AnLianDB.common.mysqlconn());

                if (dsCA != null && dsCA.Tables.Count > 0 && dsCA.Tables[0].Rows.Count > 0)
                {
                    DbHelperSQL.ExecuteSql(sqld, UFIDA.U8C.AnLianDB.common.bankconn());
                    DbHelperSQL.ImportDataByDataTable(dsCA.Tables[0], "wazh_cazj", UFIDA.U8C.AnLianDB.common.bankconn());
                    List<UFIDA.MYSQL.DB.CommandInfo> cmdlist = new List<UFIDA.MYSQL.DB.CommandInfo>();
                    foreach (DataRow item in dsCA.Tables[0].Rows)
                    {
                        cmdlist.Add(new UFIDA.MYSQL.DB.CommandInfo("update wazh_cazj set iflag = 1 where id = " + item["id"].ToString(), null));
                    }
                    UFIDA.MYSQL.DB.DbHelperSQL.ExecuteSqlTran(cmdlist, UFIDA.U8C.AnLianDB.common.mysqlconn());
                    MessageBox.Show("导入海象数据完成");
                }
                else
                {
                    MessageBox.Show("没有查询到海象数据");
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        /// <summary>
        /// excel转换datatable
        /// </summary>
        /// <param name="FileFullPath"></param>
        /// <returns></returns>
        private System.Data.DataTable GetExcelToDataTableBySheet(string FileFullPath, string sheetName, bool isFirstRowColumn)
        {
            ISheet sheet = null;
            System.Data.DataTable data = new System.Data.DataTable();
            int startRow = 0;
            HSSFWorkbook HSFbook = null;
            XSSFWorkbook XSFbook = null;
            ICell cell = null;
            try
            {
                FileStream fs = new FileStream(FileFullPath, FileMode.Open, FileAccess.Read);
                if (FileFullPath.IndexOf(".xlsx") > 0)
                { // 2007版本
                    XSFbook = new XSSFWorkbook(fs);
                    if (!string.IsNullOrEmpty(sheetName))
                    {
                        sheet = XSFbook.GetSheet(sheetName);
                    }
                    else
                    {
                        sheet = XSFbook.GetSheetAt(0);
                    }
                }

                else if (FileFullPath.IndexOf(".xls") > 0) // 2003版本
                {
                    HSFbook = new HSSFWorkbook(fs);
                    if (!string.IsNullOrEmpty(sheetName))
                    {
                        sheet = HSFbook.GetSheet(sheetName);
                    }
                    else
                    {
                        sheet = HSFbook.GetSheetAt(0);
                    }
                }


                if (sheet != null)
                {
                    int rowCount = sheet.LastRowNum;
                    if (rowCount <= 1)
                    {
                        throw new Exception("行数不对，请检查是否有标题行及字段名行");
                    }
                    IRow captionrow = sheet.GetRow(0);
                    IRow firstRow = sheet.GetRow(1);
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
                    if (isFirstRowColumn)
                    {

                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);
                            data.Columns.Add(column);
                        }
                        startRow = firstRow.RowNum + 1;
                    }
                    else
                    {
                        startRow = sheet.FirstRowNum;
                    }
                    //最后一列的标号

                    for (int i = startRow; i <= rowCount; ++i)
                    {

                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　

                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            cell = row.GetCell(j);
                            if (cell != null)
                            {
                                switch (cell.CellType)
                                {
                                    case CellType.Blank:
                                        dataRow[j] = "";
                                        break;
                                    case CellType.Numeric:
                                        short format = cell.CellStyle.DataFormat;
                                        //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
                                        if (format == 14 || format == 31 || format == 57 || format == 58 || format == 177 || format == 181 || format == 179 || format == 178 || format == 176)
                                            dataRow[j] = cell.DateCellValue.ToString("yyyy-MM-dd");
                                        else
                                            dataRow[j] = cell.NumericCellValue;
                                        break;
                                    case CellType.String:
                                        dataRow[j] = cell.StringCellValue;
                                        break;
                                }
                            }
                            else
                            {
                                dataRow[j] = "";
                            }
                            //if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                            //    dataRow[j] = row.GetCell(j).ToString();
                        }
                        data.Rows.Add(dataRow);
                    }
                }
                return data;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                //Console.WriteLine("Exception: " + ex.Message);
                return null;
            }

        }


        private void btnImportExcel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (cbcorp.Text.IsNullOrEmpty())
            {
                MessageBox.Show("请先选择公司！");
                return;
            }
            string dbgndate = edtBgndate.Text;
            string denddate = edtEnddate.Text;
            if (string.IsNullOrEmpty(dbgndate) || string.IsNullOrEmpty(denddate))
            {
                MessageBox.Show("请先选择开始结束日期");
                return;
            }
            string sql = "delete from wazh_cazj where ddate >= '" + dbgndate + "' and ddate <='" + denddate + "'";
            string strExcelName = "";
            try
            {
                openFileDialog1.FileName = "";
                openFileDialog1.Filter = "All files (*.*)|*.*|Excel files (*.xls)|*.xls|Excel files (*.xlsx)|*.xlsx";
                openFileDialog1.FilterIndex = 3;
                openFileDialog1.RestoreDirectory = true;
                //导入临时表
                if (openFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    //读取excel                        
                    strExcelName = this.openFileDialog1.FileName;

                    System.Data.DataTable dtfx = new System.Data.DataTable("ExcelTable");
                    dtfx = this.GetExcelToDataTableBySheet(strExcelName, "", true);
                    if (dtfx != null && dtfx.Rows.Count > 0)
                    {
                        DbHelperSQL.ExecuteSql(sql, UFIDA.U8C.AnLianDB.common.bankconn());
                        DbHelperSQL.ImportDataByDataTable2(dtfx, "wazh_cazj", UFIDA.U8C.AnLianDB.common.bankconn());
                        this.Query();
                    }
                }
            }
            catch (Exception se)
            {
                LogHelper.WriteLine(se.StackTrace);
                MessageBox.Show(se.Message);
            }
        }

        private void btnExcel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            try
            {
                SaveFileDialog saveDialog = new SaveFileDialog();
                saveDialog.DefaultExt = "xlsx";
                saveDialog.Filter = "Excel文件|*.xlsx";
                saveDialog.FileName = "项目吨天数据.xlsx";
                saveDialog.ShowDialog();
                string saveFileName = saveDialog.FileName;
                if (saveFileName.IndexOf(":") < 0) return; //被点了取消

                ExcelAdep.ExportExcel(gdvCA, true, saveFileName);

                MessageBox.Show("文件： " + saveFileName + ".xls 保存成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void cbcorp_SelectedIndexChanged(object sender, EventArgs e)
        {
            string unitname = cbcorp.Text;
            if (string.IsNullOrEmpty(unitname))
            {
                return;
            }
            dscorp.Tables[0].DefaultView.RowFilter = "unitname = '" + unitname + "'";
            _corp = dscorp.Tables[0].DefaultView[0]["unitcode"].ObjToString();
        }
    }
}